// 对账模块

const db = require('../db')
const moment = require('moment');
const schedule = require('node-schedule')
const jwt = require("jsonwebtoken");
const config = require("../config");
const rule = new schedule.RecurrenceRule()
/*  订单表
CREATE TABLE order (
  Order_id VARCHAR(8) PRIMARY KEY,
  Buyer_id VARCHAR(8),
  Seller_id VARCHAR(8),
  Amount DECIMAL(10, 2) NOT NULL,
  Creation_date DATE NOT NULL,  -- 支付记录时间
  order_state INT,
  refund INT,
  Complaint INT
);
中间流程表
CREATE TABLE module2_time_record(
  Order_id VARCHAR(8),
  Order_stage INT,
  amount DECIMAL(10, 2) NOT NULL,
  time Daytimegit config --list
 )


对账表
CREATE TABLE reconciliation (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id VARCHAR(8) NOT NULL,
  re_date DATE NOT NULL,  --对账时间
  re_state ENUM('Error', 'Correct'),
  FOREIGN KEY (order_id) REFERENCES order(Order_id)
);
*/

/*order_state 为1，3，6时有金额流动，其他没有

 */

function reconciliationLogic(results) {
  var status = true;//金额流动是否有误
  for (var i = 0; i < results.length; i++) {

    var date = new Date();
    var year = date.getFullYear();
    var month = date.getMonth() + 1;
    var day = date.getDate();
    var Hours = date.getHours();
    var Minutes = date.getMinutes();
    var Seconds = date.getSeconds();
    if (month < 10) {
      month = "0" + month;
    }
    if (day < 10) {
      day = "0" + day;
    }
    //转化为mysql date类型的字符串
    var sqltime = year + '-' + month + '-' + day + ' ' + Hours + ':' + Minutes + ':' + Seconds;
    console.log(sqltime)
    var id = results[i].order_id;
    //待支付状态或已取消状态，金额流动应为0
    if (results[i].order_state == 0 || results[i].order_state == 4) {
      //查看是否有过交易记录
      var query = `SELECT *
                   FROM module2_time_record
                   WHERE order_id = ${id}
                     and (order_state = 1 or order_state = 3 or order_state = 6);`;

      db.query(query, (error, result) => {
        if (error) {
          console.error('Error performing reconciliation:', error);
          //res.status(500).json({ error: 'Internal Server Error' });
        }
        else
          status = result.length == 0;
      })
    }
    else if (results[i].order_state == 1 || results[i].order_state == 2) {//代发货和待收货状态，资金由买家流向中间
      id = results[i].order_id;
      //查看是否有过交易记录
      query = `SELECT *
               FROM module2_time_record
               WHERE order_id = ${id}
                 and order_state = 1;`;

      db.query(query, (error, result) => {
        if (error) {
          console.error('Error performing reconciliation:', error);
          //res.status(500).json({ error: 'Internal Server Error' });
        } else {
          if (result.length == 1 && result.amount == results[i].Amount) {
            query = `SELECT *
                     FROM module2_time_record
                     WHERE order_id = ${id}
                       and (order_state = 3 or order_state = 6);`;

            db.query(query, (error, result0) => {
              if (error)
                console.error('Error performing reconciliation:', error);
              else
                status = result0.length == 0;
            })

          }
          else status = false

        }

      })
    }
    else if (results[i].order_state == 3) {//已完成订单
      if (results[i].refund == 0) {//收货不退款
        query = `SELECT *
                 FROM module2_time_record
                 WHERE order_id = ${id}
                   and (order_state = 1 or order_state = 3);`;//买方付款和卖方接收
        db.query(query, (error, result) => {
          if (error) {
            console.error('Error performing reconciliation:', error);
          }
          else {
            if (result.length != 2) status = false
            else {
              if (result[0].amount == result[1].amount) status = true;
              else status = false;
            }
            query = `SELECT *
                     FROM module2_time_record
                     WHERE order_id = ${id}
                       and order_state = 6;`;
            db.query(query, (error, result) => {
              if (error) {
                console.error('Error performing reconciliation:', error);
              } else {
                if (result.length != 0) status = false;//不应存在流动到买方的钱
              }
            })
          }

        })

      }
      else if (results[i].refund == 2) {//退款完成
        query = `SELECT *
                 FROM module2_time_record
                 WHERE order_id = ${id}
                   and (order_state = 1 or order_state = 6);`;//买方付款和中间人退款
        db.query(query, (error, result) => {
          if (error) {
            console.error('Error performing reconciliation:', error);
            //res.status(500).json({ error: 'Internal Server Error' });
          } else {
            if (result.length != 2) status = false
            else {
              if (result[0].amount == result[1].amount && result[0].order_state != result[1].order_state)
                status = true;
              else status = false;
            }
            query = `SELECT *
                     FROM module2_time_record
                     WHERE order_id = ${id}
                       and order_state = 3;`;
            db.query(query, (error, result) => {
              if (error) {
                console.error('Error performing reconciliation:', error);
              } else {
                if (result.length != 0) status = false;//不应存在流动到卖方的钱
              }
            })

          }
        })

      }


    }
    if(status){
      query = 'insert into reconciliation set ?'
      db.query(query, {order_id: results[i].order_id, re_date: sqltime, re_state: "Correct"}, (err, results) => {
        if(err)
          console.error('Error performing insert reconciliation:', err);
      })
    }
    else{
      query = 'insert into reconciliation set ?'
      db.query(query, {order_id: results[i].order_id, re_date: sqltime, re_state: "Error"}, (err, results) => {
        if(err)
          console.error('Error performing insert reconciliation:', err);
      })
      query = 'insert into audits set ?'
      db.query(query, {payment_id: results[i].order_id, aud_status: "待审核"}, (err, results) => {
        if(err)
          console.error('Error performing insert reconciliation:', err);
      })
    }


  }



  console.log("update table successfully")
  return {result: "data has been written successfully"};
}


/*config:{//参数的说明
  time: "14:00:00" //执行的时间点 时在0~23之间
}*/
const getReconciliationrecord = (req, res) =>{
  const module3Token = req.cookies['module3_token'];
  if (!module3Token) {
    res.send({
      status: 1,
      message: 'Token not found',
    });
  } else {
    const decoded = jwt.verify(module3Token, config.jwtSecretKeyM3);
    const user_id = decoded.user_id;
    var is_inspector;
    const q = `SELECT is_inspector FROM module3_users WHERE user_id = ?`;
    db.query(q, user_id, (err, results) => {
      if (err) {
        res.status(500).json({status: 1, message: 'Internal Server Error'});
      }

      if (results.length === 0) {
        res.status(404).json({status: 1, message: 'User not found'});
      }
      is_inspector = results[0].is_inspector;
      if (is_inspector === 0) {
        res.status(400).json({status: 1, message: 'Not inspector'});
      } else {
        var time = req.query.date;
        const query = `SELECT order_id, buyer_id, seller_id, amount, creation_date, order_state, re_state 
                 FROM module2_order natural join reconciliation where creation_date like '${time}%';`
        // console.log(query)
        db.query(query, (error, results) => {
          if (error) {
            console.error('Error getting reconciliation:', error);
            res.status(500).json({error: 'Internal Server Error'});
          } else {
            // 返回对账记录
            // console.log(results)
            results.forEach((item) => {
              item.creation_date = moment(item.creation_date).format('YYYY-MM-DD');
              switch (item.order_state) {
                case 0:
                  item.order_state = "待支付"
                  break
                case 1:
                  item.order_state = "待发货"
                  break
                case 2:
                  item.order_state = "待收货"
                  break
                case 3:
                  item.order_state = "已完成"
                  break
                case 4:
                  item.order_state = "已取消"
                  break
                default:
                  item.order_state = "未知状态"
                  break
              }
            })
            res.json({reconciliation: results, status: 200});
          }
        });
      }
    })
  }

}


const job0 =  schedule.scheduleJob('0 * * * * *', function() {
  query = `select check_time from  settings;`;
  db.query(query, (error, results) => {
    if (error) {
      console.error('Error setting reconciliation time:', error);
    } else {
      // 返回设置结果
      if(results.length == 0) console.log("Error in finding check time")
      // console.log(results[0])
      var temp = results[0].check_time
      arr = temp.split(":")
      //console.log(temp)
      //console.log(arr)
      rule.hour = arr[0]
      rule.minute = arr[1]
      rule.second = 0
      // console.log(rule)
    }

  });

  // console.log("set time");
});







const job1 =  schedule.scheduleJob(rule, function (){
  // 查询支付记录表并进行对账
  var time = (new Date).getTime() - 24 * 60 * 60 * 1000;//获取前一天的日期
  var date = new Date(time);
  var year=date.getFullYear();
  var month=date.getMonth() + 1;
  if(month <　10) month = "0" + month
  var day=date.getDate();
  if(day < 10) day = "0" + day
  var sqltime=year+'-'+month+"-"+day;
  console.log(sqltime)
  const query = `SELECT * FROM module2_order WHERE creation_date like '${sqltime}%';`;
  console.log(query)

  db.query(query, (error, results) => {
    if (error) {
      console.error('Error performing reconciliation:', error);
      return;
    } else {
      const reconciliationResult = reconciliationLogic(results);
      // 返回对账结果
      return;
    }
  });
});

const setReconciliationTime = (req, res) => {
  const module3Token = req.cookies['module3_token'];
  if (!module3Token) {
    res.send({
      status: 1,
      message: 'Token not found',
    });
  } else {
    const decoded = jwt.verify(module3Token, config.jwtSecretKeyM3);
    const user_id = decoded.user_id;
    var is_inspector;
    const q = `SELECT is_inspector FROM module3_users WHERE user_id = ?`;
    db.query(q, [user_id], (err, results) => {
      if (err) {
        res.status(500).json({status: 1, message: 'Internal Server Error'});
      }

      if (results.length === 0) {
        res.status(404).json({status: 1, message: 'User not found'});
      }
      is_inspector = results[0].is_inspector;
      if (is_inspector === 0) {
        res.status(400).json({status: 1, message: 'Not inspector'});
      } else {
        // console.log(req.body)
        var reconciliationTime = req.body.date;
        // console.log(reconciliationTime)
        // 更新对账时间设置
        var query = `select * from settings;`;
        console.log(query)

        db.query(query, (error, results) => {
          if (error) {
            console.error('Error setting reconciliation time:', error);
            return res.status(500).json({error: 'Internal Server Error'});
          } else {
            console.log(results)
            // 返回设置结果
            if (results.length == 0) {
              console.log("first data")
              query = `insert into settings values('${reconciliationTime}') ;`;
              db.query(query, (error, results) => {
                if (error) {
                  console.error('Error setting reconciliation time:', error);
                  return res.json({message: 'update failed', status: 500});
                } else
                  return res.json({message: 'Reconciliation time has been set', status: 200})
              });
            } else {
              console.log("update")
              query = `UPDATE settings SET check_time = '${reconciliationTime}';`;
              db.query(query, (error, results) => {
                if (error) {
                  console.error('Error setting reconciliation time:', error);
                  return res.json({message: 'update failed', status: 500});
                } else {
                  // 返回设置结果
                  return res.json({message: 'Reconciliation time has been set', status: 200});
                }

              });

            }

          }

        });
      }
    })
  }

};

module.exports = {
  setReconciliationTime,
  getReconciliationrecord
};

